USE WorksManagerment
GO

/*delete all store procedure*/ 
DECLARE @sql VARCHAR(MAX)=''; 

SELECT @sql = @sql + 'drop procedure [' + name + '];' 
FROM   sys.objects 
WHERE  type = 'p' 
       AND is_ms_shipped = 0 
EXEC(@sql); 
GO

/*1- Login to system*/
CREATE PROCEDURE SP_Login
	@username varchar(32),
	@password varchar(32)
AS
	SELECT m.MemberId, m.Account, r.RoleName 
	FROM (Member m JOIN Role r ON m.RoleId = r.RoleId) s
	WHERE m.Account = @username AND m.Password = @password
GO

/*2- Registry new account*/
CREATE PROCEDURE SP_Registry
	@username varchar(32),
	@password varchar(32),
	@email varchar(100),
	@roleId int
AS
	BEGIN TRAN
	BEGIN TRY
		INSERT INTO Member(Account, Password, Email, RoleId)
		VALUES(@username, @password, @email, @roleId)
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*3- Load work and paging*/
CREATE PROCEDURE SP_LoadPage
	@PageIndex int,
	@PageSize int,
	@PageCount int output
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @Total int;
		SELECT @Total = COUNT(*) FROM Work;
		SET @PageCount = CEILING(CAST(@Total AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

		SELECT s.WorkId, s.CompanyName, s.Address, s.Title, s.Description,  s.TypeName, s.Account, s.DatePosted, s.CityName FROM (
			SELECT ROW_NUMBER() OVER(ORDER BY WorkId desc) As RowNum, w.WorkId, e.CompanyName, w.Address, w.Title, w.Description,  wt.TypeName, m.Account, w.DatePosted, c.CityName
			FROM Work w JOIN Recruiter e ON w.RecruiterId = e.RecruiterId JOIN WorkType wt ON w.WorkType = wt.TypeId JOIN Member m ON e.MemberId = m.MemberId JOIN City c ON w.Location = c.CityId
		) s
		WHERE RowNum BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize
	END
GO

/*4- Work was posted by recruiter*/
CREATE PROCEDURE SP_Post
	@Location int,
	@RecruiterId int,
	@WorkType int,
	@Position int,
	@Salary nvarchar(30),
	@Description nvarchar(MAX),
	@RequiredSex nvarchar(5),
	@RequiredAge varchar(7),
	@RequiredLiteracy varchar(30),
	@RequiredNumber int,
	@RequiredLaguage nvarchar(200), 
	@RequiredAnother nvarchar(1000),
	@Phone varchar(12),
	@Email varchar(100),
	@Address nvarchar(200)
AS
	BEGIN TRAN
	BEGIN TRY
		INSERT INTO Work(Location, RecruiterId, WorkType, Position, Salary, Description, RequiredSex, RequiredAge, 
						RequiredLiteracy, RequiredNumber, RequiredLaguage, RequiredAnother, Phone, Email,
						Address, DatePosted)
		VALUES(@Location, @RecruiterId, @WorkType, @Position, @Salary, @Description, @RequiredSex, @RequiredAge,
			  @RequiredLiteracy, @RequiredNumber, @RequiredLaguage,  @RequiredAnother, @Phone, @Email, @Address,
			  GETDATE())
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*5- Work was confirmed by admin*/
CREATE PROCEDURE SP_Confirm
	@WorkId int,
	@DateClose datetime
AS
	BEGIN TRAN
	BEGIN TRY
		DECLARE @expire datetime = GETDATE();
		UPDATE Work
		SET DateConfirmed = GETDATE(), DateClose = @DateClose, Status = 0, DateExpired = DATEADD(mm, 3, @expire)
		WHERE WorkId = @WorkId
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*6- Work edit*/
CREATE PROCEDURE SP_WorkUpdate
	@WorkId int,
	@Location int,
	@WorkType int,
	@Position int,
	@Salary nvarchar(30),
	@Description nvarchar(MAX),
	@RequiredSex nvarchar(5),
	@RequiredAge varchar(7),
	@RequiredLiteracy varchar(30),
	@RequiredNumber int,
	@RequiredLaguage nvarchar(200), 
	@RequiredAnother nvarchar(1000),
	@Phone varchar(12),
	@Email varchar(100),
	@Address nvarchar(200),
	@FixBy int
AS
	BEGIN TRAN
	BEGIN TRY
		UPDATE Work
		SET DateFixed = GETDATE(), Location = @Location, WorkType = @WorkType,
			Position = @Position, Salary = @Salary, Description = @Description, RequiredSex = @RequiredSex,
			RequiredAge = @RequiredAge, RequiredAnother = @RequiredAnother, RequiredLaguage = @RequiredLaguage, 
			RequiredLiteracy = @RequiredLiteracy, RequiredNumber = @RequiredNumber, Phone = @Phone, Email = @Email,
			Address = @Address, FixBy = @FixBy
		WHERE WorkId = @WorkId
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*7- change active Work by admin*/
CREATE PROCEDURE SP_AdminActive
	@WorkId int,
	@MemberId int
AS
	BEGIN TRAN
	BEGIN TRY
		DECLARE @CurrentStatus int;
		SELECT @CurrentStatus = Status FROM Work WHERE WorkId = @WorkId
		IF (@CurrentStatus = 0)
		BEGIN
			UPDATE Work
			SET DateClose = GETDATE(), Status = 3, FixBy = @MemberId
			WHERE WorkId = @WorkId
		END
		ELSE 
		BEGIN
			UPDATE Work
			SET Status = 0, FixBy = @MemberId
			WHERE WorkId = @WorkId
		END
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*8- change status Work by filter*/
CREATE PROCEDURE SP_FilterCheck
	@WorkId int,
	@Level int
AS
	BEGIN TRAN
	BEGIN TRY
		IF (@Level = 1)
		BEGIN
			UPDATE Work
			SET Status = 4
			WHERE WorkId = @WorkId
		END
		ELSE IF (@Level = 2)
		BEGIN
			UPDATE Work
			SET Status = 5
			WHERE WorkId = @WorkId
		END
		ELSE 
		BEGIN
			UPDATE Work
			SET Status = 6
			WHERE WorkId = @WorkId
		END
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*9- check status Work by system*/
CREATE PROCEDURE SP_SystemCheck
AS
	BEGIN TRAN
	BEGIN TRY
		DECLARE @CurrentDate datetime = GETDATE();
		SELECT  WorkId 
		FROM Work
		WHERE DATEDIFF(day, DateExpired, @CurrentDate) = 0 AND Status = 0
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*10- change status Work by system*/
CREATE PROCEDURE SP_SystemClose
	@WorkId int
AS
	BEGIN TRAN
	BEGIN TRY
		UPDATE Work
		SET Status = 1
		WHERE WorkId = @WorkId
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*11- Change role*/
CREATE PROCEDURE SP_ChangeMemberRole
	@MemberId int,
	@RoleId int
AS
	BEGIN TRAN
	BEGIN TRY
		UPDATE Member
		SET RoleId = @RoleId
		WHERE MemberId = @MemberId
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*12- search work*/
CREATE PROCEDURE SP_Search
	@SearchKey nvarchar(200),
	@WorkType int,
	@Location int,
	@PageIndex int,
	@PageSize int,
	@PageCount int output
AS
	BEGIN
		/*make dynamic query*/
		DECLARE @CMD nvarchar(MAX);
		DECLARE @WHERE nvarchar(MAX);
		DECLARE @PAGING nvarchar(MAX);
		SET @CMD = '
		SET NOCOUNT ON;
		DECLARE @Total int;
		SELECT @Total = COUNT(*) FROM Work'
		SET @WHERE = ''
		IF @SearchKey IS NOT NULL 
			SET @WHERE = @WHERE + 'AND Description LIKE ' + '%' + '@SearchKey' + '%'
								+ ' OR Salary LIKE ' + '%' + '@SearchKey' + '%'
								+ ' OR RequiredLiteracy LIKE ' + '%' + '@SearchKey' + '%'
								+ ' OR RequiredLaguage LIKE ' + '%' + '@SearchKey' + '%'
								+ ' OR RequiredAnother LIKE ' + '%' + '@SearchKey' + '%'
								+ ' OR RecruiterId IN (SELECT R.RecruiterId FROM Recruiter R WHERE R.CompanyName LIKE ' + '%' + '@SearchKey' + '%' + ') ';
		IF @WorkType IS NOT NULL SET @WHERE = @WHERE + 'AND WorkType = @WorkType';
		IF @Location IS NOT NULL SET @WHERE = @WHERE + 'AND Location = @Location';
		SET @PAGING = '
		SET @PageCount = CEILING(CAST(@Total AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

		SELECT s.WorkId, s.CompanyName, s.Address, s.Title, s.Description,  s.TypeName, s.Account, s.DatePosted, s.CityName FROM (
			SELECT ROW_NUMBER() OVER(ORDER BY WorkId desc) As RowNum, w.WorkId, e.CompanyName, w.Address, w.Title, w.Description,  wt.TypeName, m.Account, w.DatePosted, c.CityName
			FROM Work w JOIN Recruiter e ON w.RecruiterId = e.RecruiterId JOIN WorkType wt ON w.WorkType = wt.TypeId JOIN Member m ON e.MemberId = m.MemberId JOIN City c ON w.Location = c.CityId
		) s
		WHERE RowNum BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize'
		IF LEN(@WHERE) > 0 SET @CMD = @CMD + ' WHERE ' + RIGHT(@WHERE,LEN(@WHERE) - 3) + @PAGING

		/*execute sp with dynamic*/
		EXEC sp_executesql @CMD, 
			N'@SearchKey nvarchar(200),
			@WorkType int,
			@Location int,
			@PageIndex int,
			@PageSize int,
			@PageCount int output',
			@SearchKey = @SearchKey,
			@WorkType = @WorkType,
			@Location = @Location,
			@PageIndex = @PageIndex,
			@PageSize = @PageSize
	END
GO

/*13- change status Work by user*/
CREATE PROCEDURE SP_UserActive
	@WorkId int,
	@MemberId int
AS
	BEGIN TRAN
	BEGIN TRY
		DECLARE @CurrentStatus int;
		SELECT @CurrentStatus = Status FROM Work WHERE WorkId = @WorkId
		IF (@CurrentStatus = 0)
		BEGIN
			UPDATE Work
			SET Status = 2, FixBy = @MemberId
			WHERE WorkId = @WorkId
		END
		ELSE IF (@CurrentStatus = 2)
		BEGIN
			UPDATE Work
			SET Status = 0, FixBy = @MemberId
			WHERE WorkId = @WorkId
		END
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*14- delete status Work by admin*/
CREATE PROCEDURE SP_AdminDelete
	@WorkId int,
	@MemberId int
AS
	BEGIN TRAN
	BEGIN TRY
		UPDATE Work
		SET Status = 7, FixBy = @MemberId
		WHERE WorkId = @WorkId
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO

/*15- check exist account*/
CREATE PROCEDURE SP_CheckExistAccount
	@Account int
AS
	SELECT * FROM Member WHERE Account = @Account
GO

/*16- check exist email*/
CREATE PROCEDURE SP_CheckExistEmail
	@Email int
AS
	SELECT * FROM Member WHERE Email = @Email
GO

/*17- change password*/
CREATE PROCEDURE SP_ChangePassword
	@MemberId int,
	@Password varchar(32)
AS
	BEGIN TRAN
	BEGIN TRY
		UPDATE Member
		SET Password = @Password
		WHERE MemberId = @MemberId
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
GO